package cn.jiangjiesheng.bootstrap.fileport.infrastructure.utils;

import com.google.common.base.Strings;
import com.google.common.collect.Lists;
import com.google.common.collect.Table;
import com.google.common.collect.TreeBasedTable;
import cn.jiangjiesheng.bootstrap.commons.exception.BizzException;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * @created by daijinchi
 * @Date 2020/1/3
 * @DESC
 */
@Slf4j
public class ExcelUtil {

    private static final String XLS = "xls";
    private static final String XLSX = "xlsx";

    /**
     * 读取excel文件
     *
     * @param input
     * @param fileName
     * @return
     * @throws Exception
     */
    public static List<Table<Integer, Integer, String>> readExcel(InputStream input, String fileName) throws Exception {
        try {
            Workbook workbook = WorkbookFactory.create(input);
            return readDataFromWorkbook(workbook);
        } catch (Exception e) {
            // 出现异常使用文件名判断类型
            if (fileName.endsWith(XLS)) {
                return readXls(input);
            } else if (fileName.endsWith(XLSX)) {
                return readXlsx(input);
            }
            throw new BizzException("文件格式错误");
        }
    }

    /**
     * 只读取表格中第一个Sheet
     *
     * @param input
     * @param fileName
     * @return
     * @throws Exception
     */
    public static Table<Integer, Integer, String> readExcelFromFirstSheet(InputStream input, String fileName) throws Exception {
        try {
            Workbook workbook = WorkbookFactory.create(input);
            return readDataFromWorkbookFirstSheet(workbook);
        } catch (Exception e) {
            if (fileName.endsWith(XLS)) {
                return readXlsFromFirstSheet(input);
            } else if (fileName.endsWith(XLSX)) {
                return readXlsxFromFirstSheet(input);
            }

            throw new BizzException("文件格式错误");
        }
    }

    /**
     * 读取xls文件
     *
     * @param input
     * @return
     * @throws Exception
     */
    public static List<Table<Integer, Integer, String>> readXls(InputStream input) throws Exception {
        List<Table<Integer, Integer, String>> list = null;
        try (Workbook workbook = new XSSFWorkbook(input)) {
            list = readDataFromWorkbook(workbook);
        }
        return list;
    }

    /**
     * 读取xls文件第一个工作簿
     *
     * @param input
     * @return
     * @throws Exception
     */
    public static Table<Integer, Integer, String> readXlsFromFirstSheet(InputStream input) throws Exception {
        try (HSSFWorkbook workbook = new HSSFWorkbook(input)) {
            return readDataFromWorkbookFirstSheet(workbook);
        }
    }


    /**
     * 读取xlsx文件
     *
     * @param input
     * @return
     * @throws Exception
     */
    public static List<Table<Integer, Integer, String>> readXlsx(InputStream input) throws Exception {
        List<Table<Integer, Integer, String>> list = null;
        try (Workbook workbook = new XSSFWorkbook(input)) {
            list = readDataFromWorkbook(workbook);
        }
        return list;
    }

    /**
     * 读取xlsx第一个工作簿
     *
     * @param input
     * @return
     * @throws Exception
     */
    public static Table<Integer, Integer, String> readXlsxFromFirstSheet(InputStream input) throws Exception {
        try (Workbook workbook = new XSSFWorkbook(input)) {
            return readDataFromWorkbookFirstSheet(workbook);
        }
    }

    /**
     * 读取工作簿
     *
     * @param workbook
     * @return
     */
    public static List<Table<Integer, Integer, String>> readDataFromWorkbook(Workbook workbook) {
        List<Table<Integer, Integer, String>> list = Lists.newArrayList();
        for (Sheet sheet : workbook) {
            if (sheet == null) {
                continue;
            }
            list.add(readDataFromSheet(sheet));
        }
        return list;
    }

    /**
     * 读取第一个工作簿
     *
     * @param workbook
     * @return
     * @throws Exception
     */
    public static Table<Integer, Integer, String> readDataFromWorkbookFirstSheet(Workbook workbook) throws Exception {
        Sheet sheet = workbook.getSheetAt(0);
        if (sheet != null) {
            return readDataFromSheet(sheet);
        } else {
            return null;
        }
    }

    /**
     * 读取数据文件
     *
     * @param sheet
     * @return
     */
    public static Table<Integer, Integer, String> readDataFromSheet(Sheet sheet) {
        Table<Integer, Integer, String> table
                = TreeBasedTable.create();
        for (int rowIndex = 0; rowIndex <= sheet.getLastRowNum(); rowIndex++) {
            Row row = sheet.getRow(rowIndex);
            if (row == null) {
                continue;
            }
            for (int cellIndex = 0; cellIndex <= row.getLastCellNum(); cellIndex++) {
                Cell cell = row.getCell(cellIndex);
                if (cell == null) {
                    continue;
                }
                cell.setCellType(CellType.STRING);
                table.put(rowIndex, cellIndex, cell.getStringCellValue());

            }
        }
        return table;
    }


    public static HSSFWorkbook buildHssfWordbookFromMap(String sheetName, List<Map<String, String>> dataList) {
        if (dataList.size() > 0) {
            Map<String, String> data = dataList.get(0);
            List<String> headNames = new ArrayList<String>();
            headNames.addAll(data.keySet());
            return buildHssfWordbookFromMap(sheetName, headNames, dataList);
        } else {
            return null;
        }
    }

    public static HSSFWorkbook buildHssfWordbookFromMap(String sheetName, List<String> headNames,
                                                        List<Map<String, String>> dataList) {
        HSSFWorkbook excel = new HSSFWorkbook();
        // 创建表
        // 1.创建一个工作薄
        HSSFSheet sheet = excel.createSheet(sheetName);
        // 5.设置标题
        HSSFRow title = sheet.createRow(0);
        for (int i = 0; i < headNames.size(); i++) {
            HSSFCell cell = title.createCell(i);
            cell.setCellValue(headNames.get(i));
        }
        if (dataList.size() > 0) {
            for (int i = 0; i < dataList.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                Map<String, String> data = dataList.get(i);
                for (int j = 0; j < headNames.size(); j++) {
                    String key = headNames.get(j);
                    HSSFCell cell = row.createCell(j);
                    if (data.containsKey(key)) {
                        cell.setCellValue(data.get(key));
                    } else {
                        cell.setCellValue("");
                    }
                }
            }
        }
        return excel;
    }

    public static void readExcelXlsFileToMap(InputStream inputStream, List<String> headNames,
                                             List<Map<String, String>> dataList) throws IOException {
        HSSFWorkbook hw = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = hw.getSheetAt(0);
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            HSSFRow row = sheet.getRow(j);
            if (j == 0) {
                for (int k = 0; k <= row.getLastCellNum(); k++) {
                    String headName = readCellStringValue(row.getCell(k));
                    if (!Strings.isNullOrEmpty(headName)) {
                        headNames.add(headName);
                    } else {
                        break;
                    }
                }
            } else {
                Map<String, String> data = new HashMap<String, String>();
                for (int k = 0; k < headNames.size(); k++) {
                    data.put(headNames.get(k), readCellStringValue(row.getCell(k)));
                }
                dataList.add(data);
            }
        }
        hw.close();

    }

    public static List<Map<String, String>> readExcelXlsFileToMap(InputStream inputStream, int skipRow)
            throws IOException {
        return readExcelXlsFileToMap(inputStream, "", skipRow, null);
    }

    public static List<Map<String, String>> readExcelXlsFileToMap(InputStream inputStream, int skipRow,
                                                                  Map<Pattern, String> headNamePatterMap) throws IOException {
        return readExcelXlsFileToMap(inputStream, "", skipRow, headNamePatterMap);
    }

    public static List<Map<String, String>> readExcelXlsFileToMap(InputStream inputStream, String sheetName,
                                                                  int skipRow, Map<Pattern, String> headNamePatterMap) throws IOException {
        HSSFWorkbook hw = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = null;
        if (Strings.isNullOrEmpty(sheetName)) {
            sheet = hw.getSheetAt(0);
        } else {
            sheet = hw.getSheet(sheetName);
        }
        List<String> headNames = new ArrayList<String>();
        List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
        for (int i = skipRow; i <= sheet.getLastRowNum(); i++) {
            HSSFRow row = sheet.getRow(i);
            if (i == skipRow) {
                for (int k = 0; k <= row.getLastCellNum(); k++) {
                    String headName = readCellStringValue(row.getCell(k));
                    if (!Strings.isNullOrEmpty(headName)) {
                        boolean find = false;
                        if (headNamePatterMap != null) {
                            for (Pattern pattern : headNamePatterMap.keySet()) {
                                if (pattern.matcher(headName).matches()) {
                                    find = true;
                                    headNames.add(headNamePatterMap.get(pattern));
                                    break;
                                }
                            }
                        }
                        if (!find) {
                            headNames.add(headName);
                        }
                    } else {
                        break;
                    }
                }
            } else {
                Map<String, String> data = new HashMap<String, String>();
                for (int k = 0; k < headNames.size(); k++) {
                    data.put(headNames.get(k), readCellStringValue(row.getCell(k)));
                }
                dataList.add(data);
            }
        }
        hw.close();
        return dataList;
    }

    public static List<Map<String, String>> readExcelXlsxFileToMap(InputStream inputStream, int skipRow,
                                                                   Map<Pattern, String> headNamePatterMap) throws IOException {
        return readExcelXlsxFileToMap(inputStream, "", skipRow, headNamePatterMap);
    }

    public static List<Map<String, String>> readExcelXlsxFileToMap(InputStream inputStream, String sheetName,
                                                                   int skipRow, Map<Pattern, String> headNamePatterMap) throws IOException {
        XSSFWorkbook hw = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = null;
        if (Strings.isNullOrEmpty(sheetName)) {
            sheet = hw.getSheetAt(0);
        } else {
            sheet = hw.getSheet(sheetName);
        }
        List<String> headNames = new ArrayList<String>();
        List<Map<String, String>> dataList = new ArrayList<Map<String, String>>();
        for (int i = skipRow; i <= sheet.getLastRowNum(); i++) {
            int readColumn = 0;
            try {
                XSSFRow row = sheet.getRow(i);
                if (row == null) {
                    continue;
                }
                if (i == skipRow) {
                    for (int k = 0; k <= row.getLastCellNum(); k++) {
                        String headName = readCellStringValue(row.getCell(k));
                        if (!Strings.isNullOrEmpty(headName)) {
                            boolean find = false;
                            if (headNamePatterMap != null) {
                                for (Pattern pattern : headNamePatterMap.keySet()) {
                                    if (pattern.matcher(headName).matches()) {
                                        find = true;
                                        headNames.add(headNamePatterMap.get(pattern));
                                        break;
                                    }
                                }
                            }
                            if (!find) {
                                headNames.add(headName);
                            }
                        } else {
                            break;
                        }
                    }
                } else {
                    Map<String, String> data = new HashMap<String, String>();
                    for (int k = 0; k < headNames.size(); k++) {
                        readColumn = k;
                        data.put(headNames.get(k), readCellStringValue(row.getCell(k)));
                    }
                    dataList.add(data);
                }
            } catch (Exception ex) {
                log.error("文件读取错误@" + i + "," + readColumn);
                throw ex;
            }
        }
        hw.close();
        return dataList;
    }

    public static HSSFWorkbook buildHssfWordbook(String sheetName, List<String> headNames,
                                                 List<List<String>> dataList) {
        HSSFWorkbook excel = new HSSFWorkbook();
        // 创建表
        // 1.创建一个工作薄
        HSSFSheet sheet = excel.createSheet(sheetName);
        // 5.设置标题
        HSSFRow title = sheet.createRow(0);
        for (int i = 0; i < headNames.size(); i++) {
            HSSFCell cell = title.createCell(i);
            cell.setCellValue(headNames.get(i));
        }
        if (dataList.size() > 0) {
            for (int i = 0; i < dataList.size(); i++) {
                HSSFRow row = sheet.createRow(i + 1);
                List<String> data = dataList.get(i);
                for (int j = 0; j < data.size(); j++) {
                    HSSFCell cell = row.createCell(j);
                    cell.setCellValue(data.get(j));
                }
            }
        }
        return excel;
    }

    public static void readExcelXlsFile(InputStream inputStream, List<String> headNames, List<List<String>> dataList)
            throws IOException {
        HSSFWorkbook hw = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = hw.getSheetAt(0);
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            HSSFRow row = sheet.getRow(j);
            if (j == 0) {
                for (int k = 0; k <= row.getLastCellNum(); k++) {
                    String headName = readCellStringValue(row.getCell(k));
                    if (!Strings.isNullOrEmpty(headName)) {
                        headNames.add(headName);
                    } else {
                        break;
                    }
                }
            } else {
                List<String> data = new ArrayList<String>();
                for (int k = 0; k < headNames.size(); k++) {
                    data.add(readCellStringValue(row.getCell(k)));
                }
                dataList.add(data);
            }
        }
        hw.close();

    }

    public static List<List<String>> readExcelXlsFile(String fileName) throws IOException {
        FileInputStream is = new FileInputStream(fileName);
        List<List<String>> result = readExcelXlsFile(is);
        is.close();
        return result;
    }

    public static List<List<String>> readExcelXlsFile(InputStream inputStream) throws IOException {
        List<List<String>> result = new ArrayList<List<String>>();
        HSSFWorkbook hw = new HSSFWorkbook(inputStream);
        HSSFSheet sheet = hw.getSheetAt(0);
        for (int j = 0; j <= sheet.getLastRowNum(); j++) {
            HSSFRow row = sheet.getRow(j);
            if (row == null) {
                throw new IOException("读取Excel文件错误: 第" + (j + 1) + "行数据为空,总" + (sheet.getLastRowNum() + 1) + "行。");
            }
            List<String> items = new ArrayList<String>();
            for (int k = 0; k <= row.getLastCellNum(); k++) {
                String headName = readCellStringValue(row.getCell(k));
                if (!Strings.isNullOrEmpty(headName)) {
                    items.add(headName);
                } else {
                    items.add("");
                }
            }
            result.add(items);
        }
        hw.close();
        return result;
    }

    private static String readCellStringValue(HSSFCell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellTypeEnum() == CellType.BLANK) {
            return "";
        } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            if (cell.getBooleanCellValue()) {
                return "1";
            } else {
                return "0";
            }
        } else if (cell.getCellTypeEnum() == CellType.ERROR) {
            return "";
        } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
            return cell.getStringCellValue().trim();
        } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            if (Math.floor(Math.abs(cell.getNumericCellValue())) == Math.abs(cell.getNumericCellValue())) {
                return new DecimalFormat("#").format(cell.getNumericCellValue());
            } else {
                return new DecimalFormat("#0.00").format(cell.getNumericCellValue());
            }
        } else if (cell.getCellTypeEnum() == CellType.STRING) {
            return cell.getStringCellValue().trim();
        }
        return "";
    }

    private static String readCellStringValue(XSSFCell cell) {
        if (cell == null) {
            return "";
        }
        if (cell.getCellTypeEnum() == CellType.BLANK) {
            return "";
        } else if (cell.getCellTypeEnum() == CellType.BOOLEAN) {
            if (cell.getBooleanCellValue()) {
                return "1";
            } else {
                return "0";
            }
        } else if (cell.getCellTypeEnum() == CellType.ERROR) {
            return "";
        } else if (cell.getCellTypeEnum() == CellType.FORMULA) {
            try {
                double v = cell.getNumericCellValue();
                if (Math.floor(Math.abs(v)) == Math.abs(v)) {
                    return new DecimalFormat("#").format(v);
                } else {
                    return new DecimalFormat("#0.00").format(v);
                }
            } catch (Exception ex) {
                try {
                    return cell.getStringCellValue();
                } catch (Exception ex2) {

                }
            }
            return "单元格错误读取错误";
        } else if (cell.getCellTypeEnum() == CellType.NUMERIC) {
            if (Math.floor(Math.abs(cell.getNumericCellValue())) == Math.abs(cell.getNumericCellValue())) {
                return new DecimalFormat("#").format(cell.getNumericCellValue());
            } else {
                return new DecimalFormat("#0.00").format(cell.getNumericCellValue());
            }
        } else if (cell.getCellTypeEnum() == CellType.STRING) {
            return cell.getStringCellValue().trim();
        }
        return "";
    }
}
